﻿using System;
using System.Collections.Generic;

using CoreLab.Oracle;
using LMD.Lib.DataClass;
using LMD.Lib.Oracle;
using System.Data;


namespace DBQuery
{
    public static class NPSQuestionerTable
    {
        public static void SaveNPSQuestioner(NPSQuestionerData nfData)
        {
            DBLib dbl = new DBLib();
            SaveNPSQuestioner(dbl, nfData);
        }
        public static void SaveNPSQuestioner(DBLib dbl,NPSQuestionerData nfData)
        {
            List<OracleParameter> lsParams = new List<OracleParameter>();
            
            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_RESULT";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            if (nfData.iScore <= 6)
                lsParams[lsParams.Count - 1].Value = "DESDUKTOR";
            else if (nfData.iScore == 7 || nfData.iScore == 8)
                lsParams[lsParams.Count - 1].Value = "NORMAL";
            else
                lsParams[lsParams.Count - 1].Value = "PROMOTOR";

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_DETAIL";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = nfData.sxDetail;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_CONTACTID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = nfData.iContactId;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_SCORE";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Integer;
            lsParams[lsParams.Count - 1].Value = nfData.iScore;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_CREATE_BY";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = nfData.sxCreator;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_CREATE_DATE";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Date;
            lsParams[lsParams.Count - 1].Value = nfData.dtmDate;
            
            dbl.ExecuteProcedure("PR_I_T_NPS_QUESTIONER", lsParams.ToArray());
        }

        public static void UpdateNPSQuestioner(NPSQuestionerData nfData)
        {
            DBLib dbl = new DBLib();
            UpdateNPSQuestioner(dbl, nfData);
        }
        public static void UpdateNPSQuestioner(DBLib dbl, NPSQuestionerData nfData)
        {
            List<OracleParameter> lsParams = new List<OracleParameter>();

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_ID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = nfData.ID;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_RESULT";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            if (nfData.iScore <= 6)
                lsParams[lsParams.Count - 1].Value = "DESDUKTOR";
            else if (nfData.iScore == 7 || nfData.iScore == 8)
                lsParams[lsParams.Count - 1].Value = "NORMAL";
            else
                lsParams[lsParams.Count - 1].Value = "PROMOTOR";

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_DETAIL";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = nfData.sxDetail;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_CONTACTID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = nfData.iContactId;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_SCORE";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Integer;
            lsParams[lsParams.Count - 1].Value = nfData.iScore;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_CREATE_BY";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = nfData.sxCreator;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_CREATE_DATE";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Date;
            lsParams[lsParams.Count - 1].Value = nfData.dtmDate;

            dbl.ExecuteProcedure("PR_U_T_NPS_QUESTIONER", lsParams.ToArray());
        }

        public static void DeleteNPSQuestioner(int ID)
        {
            DBLib dbl = new DBLib();
            DeleteNPSQuestioner(dbl, ID);

        }
        public static void DeleteNPSQuestioner(DBLib dbl, int ID)
        {
            OracleParameter param = new OracleParameter();
            param.ParameterName = "prmID";
            param.OracleDbType = OracleDbType.Number;
            param.Value = ID;

            String sxQuery =
                @"
                delete from 
                    T_NPS_QUESTIONER
                where
                    T_NPS_QUESTIONER_ID = :prmID
                ";

            dbl.ExecuteNonQuery(sxQuery, param);
        }

        public static DataTable GetNPSQuestioner()
        {
            DataTable dt = new DataTable();
            DBLib dbl = new DBLib();
            String sxQuery = @"Select * from T_NPS_QUESTIONER";
            dt = dbl.GetDataTable(sxQuery);
            return dt;
        }
        public static DataTable GetNPSQuestionerByID(int ID)
        {
            DataTable dt = new DataTable();
            DBLib dbl = new DBLib();
            String sxQuery = @"Select * from T_NPS_QUESTIONER WHERE T_NPS_QUESTIONER_ID = " + ID;
            dt = dbl.GetDataTable(sxQuery);
            return dt;
        }
        public static DataTable GetNPSQuestionerByContactID(int ID)
        {
            DataTable dt = new DataTable();
            DBLib dbl = new DBLib();
            String sxQuery = @"Select * from T_NPS_QUESTIONER WHERE NEW_CONTACT_PERSON_ID = " + ID;
            dt = dbl.GetDataTable(sxQuery);
            return dt;
        }
    }
}
